#
#	This file is part of Energy@Home.
#	Copyright (C) 2009 Danny Tsang
#	
#	This file is part of Energy@Home.
#	
#	Energy@Home is free software: you can redistribute it and/or modify
#	it under the terms of the GNU General Public License as published by
#	the Free Software Foundation, either version 3 of the License, or
#	(at your option) any later version.
#	
#	Energy@Home is distributed in the hope that it will be useful,
#	but WITHOUT ANY WARRANTY; without even the implied warranty of
#	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#	GNU General Public License for more details.
#	
#	You should have received a copy of the GNU General Public License
#	along with Energy@Home.  If not, see <http://www.gnu.org/licenses/>.
#

__author__ = 'Danny Tsang <danny@dannytsang.co.uk>'

import time, sys, os
import logging, logging.config

# Append paths before importing libraries and program modules
sys.path.append("..")
sys.path.append(os.path.abspath(os.path.join(os.path.abspath(os.path.join(os.path.abspath(__file__), os.path.pardir)), os.path.pardir)) + os.sep + "lib")

import MySQLdb
from sqlalchemy import create_engine, text
from sqlalchemy.pool import NullPool

from config.Config import ConfigManager

class MySQL(object):
    
    INSTANCE = None
    
    # Singleton pattern
    def __new__(cls, *args, **kwargs):
        if not cls.INSTANCE:
            cls.INSTANCE = super(MySQL, cls).__new__(
                                cls, *args, **kwargs)
        
        return cls.INSTANCE

    def __init__(self):
        '''Check all global variables have been initialized'''
        
        # Configuration Manager
        self.CONFIG = ConfigManager()
        
        # Instantiate logging
        logging.config.fileConfig(self.CONFIG.getConfigFilePath())
        self.LOGGER = logging.getLogger("energyathome.datalogger.database.mysql")
        
        self.engine = None

    # Connect to MySQL using settings from DatabaseConfig.py
    def connect(self):
        '''Connect to database'''
        
        self.LOGGER.info("Attempting DB connection")
        databaseSettings = self.CONFIG.getConfigCategory("Database")
        
        """self.engine = create_engine(databaseSettings["connectstring"] + "://" + databaseSettings["username"] + ":" +\
                        databaseSettings["password"] + "@" + databaseSettings["url"] + ":" + databaseSettings["port"] +\
                        "/" + databaseSettings["database"], pool_size = int(databaseSettings["poolsize"]), 
                        max_overflow = int(databaseSettings["maxpoolsize"]))"""
        self.engine = create_engine(databaseSettings["connectstring"] + "://" + databaseSettings["username"] + ":" +\
                        databaseSettings["password"] + "@" + databaseSettings["url"] + ":" + databaseSettings["port"] +\
                        "/" + databaseSettings["database"], strategy='threadlocal')
        
        self.LOGGER.info("DB connection successful")

    # Close DB CONNECTION
    def disconnect(self):
        '''Close CONNECTION to database'''
        
        try:
            # Only close CONNECTION if CONNECTION exists
        if self.engine is not None:
            self.LOGGER.info("Attempting to close DB connection")
            self.engine.dispose()
            
            self.LOGGER.info("Close DB connection successful")
            
            return True
        except Exception, e:
            self.LOGGER.error("Unable to disconnect from DB:")
            self.LOGGER.error(e)
            
        
        return False

    # Execute query with no return results
    def executeNonUpdate(self, statement, values):
        '''Execute SQL with no return results.
        Accepts an SQL statement with %s parameters which will be replaced with
        the values parameter. If no %s is used, values should be set to None.
        Returns last inserted row ID reguardless of SQL type.'''
        
        self.engine.begin()
        # Open a connection to DB
        conn = self.engine.connect()
        
        # Execute query
        results = conn.execute(text(statement), values)
        
        self.engine.commit()
        
        # Close connection
        conn.close()
        
        return results.lastrowid

    # Execute query and return results
    def executeUpdate(self, statement, values):
        '''Execute query and return all results.'''
        
        
        self.engine.begin()
        # Open a connection to DB
        conn = self.engine.connect()
        
        # Execute query
        results = conn.execute(text(statement), values).fetchall()
        
        self.engine.commit()
        # Close connection
        conn.close()
        
        
        return results
        

    # Execute query and return one result
    def executeOneUpdate(self, statement, values):
        '''Execute SQL and returns one result.
        Accepts an SQL statement with %s parameters which will be replaced with
        the values parameter. If no %s is used, values should be set to None.
        Returns the first result only.'''
        
        self.engine.begin()
        
        # Open a connection to DB
        conn = self.engine.connect()
        
        # Execute query
        results = conn.execute(text(statement), values).fetchone()
        
        self.engine.commit()
        # Close connection
        conn.close()
        
        
        return results
